package com.pls.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.pls.constants.dbConstants;
import com.pls.domain.Employee;
import com.pls.domain.EmployeeWorkstation;
import com.pls.domain.Report;
import com.pls.domain.ReportCubeSummary;
import com.pls.domain.ReportFloorSummary;
import com.pls.domain.ReportSeatPlan;
import com.pls.domain.Seat;
import com.pls.utilities.ConnectionManager;

public class ReportDAOImpl implements ReportDAO{

	Logger logger = Logger.getLogger(EmployeeDAOImpl.class);
	
	private CallableStatement proc = null;
	private ResultSet rs = null;
	private ConnectionManager conManager;
	private Connection connection = null;
	
	@Override
	public List<ReportSeatPlan> getReportSeatPlanList(String location_detail_id) throws Exception {
		List<ReportSeatPlan> seatplanlist = new ArrayList<ReportSeatPlan>();
		
		try
		   {
			  conManager = ConnectionManager.getInstance();
			  connection = conManager.getConnection();
		      proc = connection.prepareCall(dbConstants.PROC_GET_SEATPLANLIST);
		      proc.setInt(1, Integer.parseInt(location_detail_id));
		      
		      logger.debug("calling stored procedure:" + proc.toString());
		      rs = proc.executeQuery();
			
			  while (rs.next()) {
				  ReportSeatPlan rsp = new ReportSeatPlan();
				  	rsp.setQUADRANT(rs.getString("Quadrant"));
					rsp.setRow(rs.getString("Row"));
					rsp.setWorkstationNo(rs.getString("Workstation"));
					rsp.setLocalNo(rs.getString("LocalNo"));
					rsp.setTeam(rs.getString("Team"));
					
					seatplanlist.add(rsp);
				}

				rs.close();
		     
		   } catch (SQLException e) {
				//Do error logs here
			   logger.error("SQLException: " + e.getMessage().toString());
				throw e;
				
				
			}  catch (ClassNotFoundException e) {
				//Do error logs here
				logger.error("ClassNotFoundException: " + e.getMessage().toString());
				throw e;
			}	finally	{
		      try
		      {
		         proc.close();
		      }
		      catch (SQLException e) {}
		      connection.close();
		   }
		logger.debug("End of getting seat plan");
		 return seatplanlist;
	}
	
	@Override
	public List<ReportCubeSummary> getReportByCubeSummary() throws Exception{
		List<ReportCubeSummary> rptCubeList = new ArrayList<ReportCubeSummary>();
		
		try
		   {
			  conManager = ConnectionManager.getInstance();
			  connection = conManager.getConnection();
		      proc = connection.prepareCall(dbConstants.PROC_GET_CUBE_SUMMARY);
		      
		      logger.debug("calling stored procedure:" + proc.toString());
		      rs = proc.executeQuery();
			
			  while (rs.next()) {
				  ReportCubeSummary rptCube = new ReportCubeSummary();
				  rptCube.setPmCubeNo(rs.getString("SeatCode"));
				  rptCube.setName(rs.getString("Name"));
				  rptCube.setLocalNo(rs.getString("LocalNo"));
				  rptCubeList.add(rptCube);
				}

				rs.close();
		     
		   } catch (SQLException e) {
				//Do error logs here
			   logger.error("SQLException: " + e.getMessage().toString());
				throw e;
				
				
			}  catch (ClassNotFoundException e) {
				//Do error logs here
				logger.error("ClassNotFoundException: " + e.getMessage().toString());
				throw e;
			}	finally	{
		      try
		      {
		         proc.close();
		      }
		      catch (SQLException e) {}
		      connection.close();
		   }
		logger.debug("End of getting cube summary report");
		return rptCubeList;
	}
	
	@Override
	public ReportFloorSummary getReportFloorSummary() throws Exception{
		 ReportFloorSummary rptFloor = new ReportFloorSummary();
		try
		   {
			  conManager = ConnectionManager.getInstance();
			  connection = conManager.getConnection();
		      proc = connection.prepareCall(dbConstants.PROC_GET_FLOOR_SUMMARY);
		      
		      logger.debug("calling stored procedure:" + proc.toString());
		      rs = proc.executeQuery();
			
			  while (rs.next()) {
				  rptFloor.setEmployeeVacantWorkstation(Integer.parseInt(rs.getString("EmployeeVacantWorkstation")));
				  rptFloor.setEmployeeOccupiedWorkstation(Integer.parseInt(rs.getString("EmployeeOccupiedWorkstation")));
				  rptFloor.setPMVacantWorkstation(Integer.parseInt(rs.getString("PMVacantWorkstation")));
				  rptFloor.setPMOccupiedWorkstation(Integer.parseInt(rs.getString("PMOccupiedWorkstation")));
				  rptFloor.setBackOffice(Integer.parseInt(rs.getString("BackOffice")));
				  rptFloor.setTrainingRoom1(Integer.parseInt(rs.getString("TrainingRoom1")));
				  rptFloor.setTrainingRoom2(Integer.parseInt(rs.getString("TrainingRoom2")));
				  rptFloor.setEORoom(Integer.parseInt(rs.getString("EORoom")));
				  rptFloor.setReception(Integer.parseInt(rs.getString("Reception")));
			  }
			  
			  rs.close();
		     
		   } catch (SQLException e) {
				//Do error logs here
			   logger.error("SQLException: " + e.getMessage().toString());
				throw e;
				
				
			}  catch (ClassNotFoundException e) {
				//Do error logs here
				logger.error("ClassNotFoundException: " + e.getMessage().toString());
				throw e;
			}	finally	{
		      try
		      {
		         proc.close();
		      }
		      catch (SQLException e) {}
		      connection.close();
		   }
		logger.debug("End of getting cube summary report");
		return rptFloor;
	}
	
	@Override
	public List<Report> getLocalSummaryReport() throws Exception {
		List<Report> rptlist = new ArrayList<Report>();
		
		try
		   {
			  conManager = ConnectionManager.getInstance();
			  connection = conManager.getConnection();
		      proc = connection.prepareCall(dbConstants.PROC_GET_LOCAL_SUMMARY);
		      
		      logger.debug("calling stored procedure:" + proc.toString());
		      rs = proc.executeQuery();
			
			  while (rs.next()) {
				  Report rpt = new Report();
				  rpt.setCategory(rs.getString("Project"));
				  rpt.setCount(Integer.parseInt(rs.getString("NumberOfWorkstation")));
				  rptlist.add(rpt);
				}

				rs.close();
		     
		   } catch (SQLException e) {
				//Do error logs here
			   logger.error("SQLException: " + e.getMessage().toString());
				throw e;
				
				
			}  catch (ClassNotFoundException e) {
				//Do error logs here
				logger.error("ClassNotFoundException: " + e.getMessage().toString());
				throw e;
			}	finally	{
		      try
		      {
		         proc.close();
		      }
		      catch (SQLException e) {}
		      connection.close();
		   }
		logger.debug("End of getting local no summary report");
		return rptlist;
	}
	
	@Override
	public List<Report> getReportSeatPerProject(String location_detail_id) throws Exception {
		List<Report> rptlist = new ArrayList<Report>();
		
		try
		   {
			  conManager = ConnectionManager.getInstance();
			  connection = conManager.getConnection();
		      proc = connection.prepareCall(dbConstants.PROC_GET_SEAT_PER_PROJECT);
		      proc.setInt(1, Integer.parseInt(location_detail_id));
		      
		      logger.debug("calling stored procedure:" + proc.toString());
		      rs = proc.executeQuery();
			
			  while (rs.next()) {
				  Report rpt = new Report();
				  rpt.setCategory(rs.getString("Project"));
				  rpt.setCount(Integer.parseInt(rs.getString("NumberOfWorkstation")));
				  rptlist.add(rpt);
				}

				rs.close();
		     
		   } catch (SQLException e) {
				//Do error logs here
			   logger.error("SQLException: " + e.getMessage().toString());
				throw e;
				
				
			}  catch (ClassNotFoundException e) {
				//Do error logs here
				logger.error("ClassNotFoundException: " + e.getMessage().toString());
				throw e;
			}	finally	{
		      try
		      {
		         proc.close();
		      }
		      catch (SQLException e) {}
		      connection.close();
		   }
		logger.debug("End of getting seat per project report");
		 return rptlist;
	}
	
	
}
